package com.lsy.demo.controller;

import cn.gjing.tools.excel.ExcelFactory;
import cn.gjing.tools.excel.metadata.ExcelType;
import cn.gjing.tools.excel.write.resolver.ExcelSimpleWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.fastjson.JSON;
import com.lsy.demo.entry.KOne;
import com.lsy.demo.entry.KOneConditionA;
import com.lsy.demo.utils.ExcelUtil;
import com.lsy.demo.utils.LogicUtils;
import org.apache.ibatis.annotations.Param;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author : lsy
 * @version 1.0
 * @description: TODO
 * @date 2021/10/29 12:41
 */

@RestController
@RequestMapping("/user")
public class ExcelBindWriteController {


    @RequestMapping(value = "/listDemo",method = RequestMethod.POST)
    public Map<String,Object> simpleWrite123(@RequestBody ExcelVO excelVO) {
        System.out.println(excelVO.getAddress() +"====="+excelVO.getDept());
        Map map;
        // tring [] asa = "【拆分 】2021年9月份-员工20210901_20210930.xlsx".split("\\.");
        String filePath = "D:\\wenan\\111.xlsx";
        if ("second".equals(excelVO.getTabName())) {
            System.out.println("=====222====="+excelVO.getTabName());
            map = LogicUtils.secondPublic(excelVO,filePath);
        } else if ("third".equals(excelVO.getTabName())) {
            System.out.println("=====333====="+excelVO.getTabName());
            map = LogicUtils.thirdPublic(excelVO,filePath);
        }  else if ("four".equals(excelVO.getTabName())) {
            System.out.println("=====444====="+excelVO.getTabName());
            map = LogicUtils.fourPublic(excelVO,filePath);
        } else {
            // 默认第一个
            System.out.println("=====111====="+excelVO.getTabName());
            map = LogicUtils.firstPublic(excelVO,filePath);
        }
        return map;
    }


    @RequestMapping(value = "/download")
    public void simpleWrite(HttpServletResponse response) throws Exception {
        //@RequestParam("address") String address,@RequestParam("dept") String dept,
        //                            @RequestParam("beginTime") String beginTime,@RequestParam("endTime") String endTime,
//        String fileName = endTime;
//        System.out.println(address +"====="+ dept + "---" +beginTime +"-----"+endTime);
        String beginTime = "1232131231";
        String fileName = "231231";
        String endTime = "1232131231";
        List<String> fileList = Arrays.asList("北京", "西安", "国贸");
        List<String> listSheet = Arrays.asList("工资总表", "大区—团队管理者","工作量汇总","当月退费—财务提供");
        ExcelFactory.createSimpleWriter(fileName, response, ExcelType.XLSX)
                    .head(this.getMultiHead1(beginTime,endTime))
                    // 和工厂的绑定模式一样，需要设置多级表头属性为true
                    .multiHead(true)
                    .write(this.getData1(),listSheet.get(0))
                    .head(this.getMultiHead2())
                    .write(this.getData2(),listSheet.get(1))
                    .head(this.getMultiHead3())
                    .write(this.getData3(),listSheet.get(2))
                    .head(this.getMultiHead4())
                    .write(this.getData4(),listSheet.get(3)).flush();
    }

    /**
     * 获取多级级表头模拟数据 工资总表
     *
     * @return 表头
     */
    private List<String[]> getMultiHead1 (String beginTime,String endTime) {
        List<String[]> arr = new ArrayList<>();
        // 1-7
        arr.add(new String[]{"需要手动更新", "工资周期", "序号"});
        arr.add(new String[]{"", beginTime, "姓名"});
        arr.add(new String[]{"", endTime, "公司"});
        arr.add(new String[]{"", "本城市最低工资", "一级部门"});
        arr.add(new String[]{"", "2320", "二级部门"});
        arr.add(new String[]{"", "2320", "三级部门"});
        arr.add(new String[]{"", "2320", "职位"});
        // 8 -16
        arr.add(new String[]{"身份证", "身份证", "身份证"});
        arr.add(new String[]{"银行卡号", "银行卡号", "银行卡号"});
        arr.add(new String[]{"开户行", "开户行", "开户行"});
        arr.add(new String[]{"员工状态", "员工状态", "员工状态"});
        arr.add(new String[]{"考核状态", "考核状态", "考核状态"});
        arr.add(new String[]{"员工类型", "员工类型", "员工类型"});
        arr.add(new String[]{"入职时间", "入职时间", "入职时间"});
        arr.add(new String[]{"考核期通过日期", "考核期通过日期", "考核期通过日期"});
        arr.add(new String[]{"离职日期", "离职日期", "离职日期"});
        // 17 - 21
        arr.add(new String[]{"基本工资", "基本工资标准", "基本工资标准"});
        arr.add(new String[]{"基本工资", "本月实发基本", "本月实发基本"});
        arr.add(new String[]{"基本工资", "底薪浮动差额", "底薪浮动差额"});
        arr.add(new String[]{"绩效工资", "绩效工资标准", "绩效工资标准"});
        arr.add(new String[]{"绩效工资", "本月实发绩效", "本月实发绩效"});
        // 22-27
        arr.add(new String[]{"提成", "提成", "提成"});
        arr.add(new String[]{"奖金", "奖金", "奖金"});
        arr.add(new String[]{"社保差额调整", "社保差额调整", "社保差额调整"});
        arr.add(new String[]{"其他税前项", "其他税前项", "其他税前项"});
        arr.add(new String[]{"退费", "退费", "退费"});
        arr.add(new String[]{"资源扣罚项（奖惩项）", "资源扣罚项（奖惩项）", "资源扣罚项（奖惩项）"});
        // 28 -33
        arr.add(new String[]{"考勤部分", "月计薪天数", "月计薪天数"});
        arr.add(new String[]{"考勤部分", "迟到", "原始次数"});
        arr.add(new String[]{"考勤部分", "迟到", "免责后次数"});
        arr.add(new String[]{"考勤部分", "迟到", "金额"});
        arr.add(new String[]{"考勤部分", "入离职缺勤部分", "天数"});
        arr.add(new String[]{"考勤部分", "入离职缺勤部分", "金额"});
        // 34 -41
        arr.add(new String[]{"考勤部分", "事假扣款", "天数"});
        arr.add(new String[]{"考勤部分", "事假扣款", "金额"});
        arr.add(new String[]{"考勤部分", "旷工扣款", "天数"});
        arr.add(new String[]{"考勤部分", "旷工扣款", "金额"});
        arr.add(new String[]{"考勤部分", "病假扣款", "天数"});
        arr.add(new String[]{"考勤部分", "病假扣款", "金额"});
        arr.add(new String[]{"考勤部分", "考勤扣款小计", "考勤扣款小计"});
        arr.add(new String[]{"应付工资", "应付工资", "应付工资"});
        return arr;
    }

    /**
     * 获取多级级表头模拟数据 大区一团队管理表
     *
     * @return 表头
     */
    private List<String[]> getMultiHead2() {
        List<String[]> arr = new ArrayList<>();
        // 1- 16
        arr.add(new String[]{"序号", "序号", "序号"});
        arr.add(new String[]{"姓名", "姓名", "姓名"});
        arr.add(new String[]{"分公司级别", "分公司级别", "分公司级别"});
        arr.add(new String[]{"公司", "公司", "公司"});
        arr.add(new String[]{"部门", "部门", "部门"});
        arr.add(new String[]{"组别", "组别", "组别"});
        arr.add(new String[]{"岗位类别", "岗位类别", "岗位类别"});
        arr.add(new String[]{"入职时间", "入职时间", "入职时间"});
        arr.add(new String[]{"考核期通过日期", "考核期通过日期", "考核期通过日期"});
        arr.add(new String[]{"离职时间", "离职时间", "离职时间"});
        arr.add(new String[]{"员工状态", "员工状态", "员工状态"});
        arr.add(new String[]{"基本工资标准", "基本工资标准", "基本工资标准"});
        arr.add(new String[]{"绩效工资标准", "绩效工资标准", "绩效工资标准"});
        arr.add(new String[]{"年度团队任务量", "年度团队任务量", "年度团队任务量"});
        arr.add(new String[]{"考核月份", "考核月份", "考核月份"});
        arr.add(new String[]{"月度团队任务量", "月度团队任务量", "月度团队任务量"});
        // 17 - 20
        arr.add(new String[]{"新签业绩", "美国签约", "美国签约"});
        arr.add(new String[]{"新签业绩", "非美签约", "非美签约"});
        arr.add(new String[]{"新签业绩", "培训签约", "培训签约"});
        arr.add(new String[]{"新签业绩", "项目签约", "项目签约"});
        // 21 - 31
        arr.add(new String[]{"新签业绩", "作品集", "作品集-留学-签约"});
        arr.add(new String[]{"新签业绩", "作品集", "作品集-培训-签约"});
        arr.add(new String[]{"新签业绩", "作品集", "作品集-产品-签约"});
        arr.add(new String[]{"新签业绩", "日本", "日本-留学-签约"});
        arr.add(new String[]{"新签业绩", "日本", "日本-培训-签约"});
        arr.add(new String[]{"新签业绩", "日本", "日本-产品-签约"});
        arr.add(new String[]{"新签业绩", "移民签约", "移民签约"});
        arr.add(new String[]{"新签业绩", "菁英规划签约*20%", "菁英规划签约*20%"});
        arr.add(new String[]{"新签业绩", "产品保录签约", "产品保录签约"});
        arr.add(new String[]{"新签业绩", "新签小计", "新签小计"});
        arr.add(new String[]{"新签完成率", "新签完成率", "新签完成率"});
        // 32 - 45
        arr.add(new String[]{"退费金额", "美国退费", "美国退费"});
        arr.add(new String[]{"退费金额", "非美退费", "非美退费"});
        arr.add(new String[]{"退费金额", "培训退费", "培训退费"});
        arr.add(new String[]{"退费金额", "项目退费", "项目退费"});
        arr.add(new String[]{"退费金额", "作品集", "作品集-留学-退费"});
        arr.add(new String[]{"退费金额", "作品集", "作品集-培训-退费"});
        arr.add(new String[]{"退费金额", "作品集", "作品集-产品-退费"});
        arr.add(new String[]{"退费金额", "日本", "日本-留学-退费"});
        arr.add(new String[]{"退费金额", "日本", "日本-培训-退费"});
        arr.add(new String[]{"退费金额", "日本", "日本-产品-退费"});
        arr.add(new String[]{"退费金额", "移民退费", "移民退费"});
        arr.add(new String[]{"退费金额", "菁英规划退费*20%", "菁英规划退费*20%"});
        arr.add(new String[]{"退费金额", "产品保录-退费", "产品保录-退费"});
        arr.add(new String[]{"退费金额", "退费小计", "退费小计"});
        // 46 - 55
        arr.add(new String[]{"", "团队净业绩", "团队净业绩"});
        arr.add(new String[]{"", "团队净业绩完成率", "团队净业绩完成率"});
        arr.add(new String[]{"", "1.业绩绩效", "1.业绩绩效"});
        arr.add(new String[]{"", "当月新签客户人数", "当月新签客户人数"});
        arr.add(new String[]{"", "当月有效资源量", "当月有效资源量"});
        arr.add(new String[]{"", "非口碑有效资源转化率（已过考核期）", "非口碑有效资源转化率（已过考核期）"});
        arr.add(new String[]{"", "2.非口碑新签绩效", "2.非口碑新签绩效"});
        arr.add(new String[]{"", "团队搭建完成率（考核期）", "团队搭建完成率（考核期）"});
        arr.add(new String[]{"", "3.团队搭建绩效", "3.团队搭建绩效"});
        arr.add(new String[]{"", "月度实发绩效合计", "月度实发绩效合计"});
        // 56 - 61
        arr.add(new String[]{"", "团队提成", "个人留学净业绩"});
        arr.add(new String[]{"", "团队提成", "团队留学净业绩"});
        arr.add(new String[]{"", "团队提成", "团队提成基数"});
        arr.add(new String[]{"", "团队提成", "团队提成金额"});
        arr.add(new String[]{"", "季度奖励合计", "季度奖励合计"});
        arr.add(new String[]{"", "本月奖金合计", "本月奖金合计"});
        arr.add(new String[]{"", "备注", "备注"});
        return arr;
    }

    /**
     * 获取多级级表头模拟数据 工作量汇总
     *
     * @return 表头
     */
    private List<String[]> getMultiHead3() {
        List<String[]> arr = new ArrayList<>();
        arr.add(new String[]{"月份"});
        arr.add(new String[]{"公司"});
        arr.add(new String[]{"合同号"});
        arr.add(new String[]{"学员姓名"});
        arr.add(new String[]{"合同名称（套餐/服务名称）"});
        arr.add(new String[]{"服务明细"});

        arr.add(new String[]{"实际收款日期"});
        arr.add(new String[]{"实际收款金额"});
        arr.add(new String[]{"计入业绩金额"});
        arr.add(new String[]{"签约国家"});
        arr.add(new String[]{"咨询顾问"});
        arr.add(new String[]{"助签顾问（两人及以上合签时填写）"});

        arr.add(new String[]{"语培促签顾问"});
        arr.add(new String[]{"BE促签顾问"});
        arr.add(new String[]{"提报类别"});
        arr.add(new String[]{"提成人员姓名"});
        arr.add(new String[]{"是否合签"});
        arr.add(new String[]{"合签业绩比例"});

        arr.add(new String[]{"提成金额"});
        arr.add(new String[]{"岗位类型"});
        arr.add(new String[]{"提报类型"});
        arr.add(new String[]{"提点/级别"});
        arr.add(new String[]{"提成合计"});
        arr.add(new String[]{"备注"});
        return arr;
    }



    /**
     * 获取多级级表头模拟数据 工作量汇总
     *
     * @return 表头
     */
    private List<String[]> getMultiHead4() {
        List<String[]> arr = new ArrayList<>();
        arr.add(new String[]{"月份"});
        arr.add(new String[]{"公司"});
        arr.add(new String[]{"合同号"});
        arr.add(new String[]{"学员姓名"});
        arr.add(new String[]{"合同名称（套餐/服务名称）"});
        arr.add(new String[]{"服务明细"});

        arr.add(new String[]{"实际收款日期"});
        arr.add(new String[]{"实际收款金额"});
        arr.add(new String[]{"计入业绩金额"});
        arr.add(new String[]{"签约国家"});
        arr.add(new String[]{"咨询顾问"});
        arr.add(new String[]{"助签顾问（两人及以上合签时填写）"});

        arr.add(new String[]{"语培促签顾问"});
        arr.add(new String[]{"BE促签顾问"});
        arr.add(new String[]{"提报类别"});
        arr.add(new String[]{"提成人员姓名"});
        arr.add(new String[]{"是否合签"});
        arr.add(new String[]{"合签业绩比例"});

        arr.add(new String[]{"提成金额"});
        arr.add(new String[]{"岗位类型"});
        arr.add(new String[]{"提报类型"});
        arr.add(new String[]{"提点/级别"});
        arr.add(new String[]{"提成合计"});
        arr.add(new String[]{"备注"});
        return arr;
    }


    /**
     * 获取模拟数据   41列
     * @return 数据
     */
    private List<List<Object>> getData1() {
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("团队A", 8, 12,"北京大区",null,null,"综合运行专员","团队A",1221321.3242, 89.0902,
                "北京大区",null,null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员","综合运行专员",
                null,"综合运行专员",null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                null,"综合运行专员",null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                213231
                ));
        data.add(Arrays.asList("团队A", 8, 12,"北京大区",null,null,"综合运行专员","团队A",1221321.3242, 89.0902,
                "北京大区",null,null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员","综合运行专员",
                null,"综合运行专员",null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                null,"综合运行专员",null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                213231));
        return data;
    }


    /**
     * 获取模拟数据
     * @return 数据
     */
    private List<List<Object>> getData2() {
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("团队A", 8, 12,"北京大区",null,null,"综合运行专员","团队A",1221321.3242, 89.0902,
                "北京大区",null,null,"综合运行专员",12,"综合运行专员","综合运行专员",null,"综合运行专员","综合运行专员",
                null,"综合运行专员",null,"综合运行专员",21,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                null,"综合运行专员",null,"综合运行专员",123,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                null,"综合运行专员",null,"综合运行专员",123,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                null,"综合运行专员",null,"综合运行专员",1231,"综合运行专员","综合运行专员",null,"综合运行专员",null,
                213231,"综合12312312323运行专员"));
        return data;
    }


    /**
     * 获取模拟数据
     * @return 数据
     */
    private List<List<Object>> getData3() {
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("团队A", 8, 12,"北京大区",null,null,"综合运行专员","团队A",1221321.3242, 89.0902,
                "北京大区",null,null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员","综合运行专员",
                null,"综合运行专员",null,"综合运行0000000000"
        ));
        return data;
    }


    /**
     * 获取模拟数据
     * @return 数据
     */
    private List<List<Object>> getData4() {
        List<List<Object>> data = new ArrayList<>();
        data.add(Arrays.asList("团队A", 8, 12,"北京大区",null,null,"综合运行专员","团队A",1221321.3242, 89.0902,
                "北京大区",null,null,"综合运行专员",null,"综合运行专员","综合运行专员",null,"综合运行专员","综合运行专员",
                null,"综合运行专员",null,"综合运行0000000000"
        ));
        return data;
    }

    public static void main(String[] args) {
        String [] asa = "【拆分 】2021年9月份23123-员工20210901_20210930.xlsx".split("\\.");
        int length = asa[0].length();
        String substring = asa[0].substring(length - 17,length);
        System.out.println(substring);
    }
}
